package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Distrib;
import com.xxxy.jsq.model.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DistribDao implements BaseDao<Distrib> {
    private C3P0Utils db = null;

    public DistribDao() {
        this.db = new C3P0Utils();
    }

    @Override
    public String add(Distrib model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("INSERT INTO pa_distrib(role_id, works_id, distrib_date, distrib_memo, distrib_flag) ");
        sqlStr.append("VALUES(?, ?, ?, ?, ?)");

        conn = db.getConnection();
        boolean flag = isSame(model, true);

        if (!flag) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getRoleId());
                pstm.setString(2, model.getWorksId());
                pstm.setDate(3, new java.sql.Date(model.getDistribDate().getTime()));
                pstm.setString(4, model.getDistribMemo());
                pstm.setString(5, model.getDistribFlag());

                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            if (num > 0) {
                return "Ok";
            } else {
                return "Err";
            }
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Distrib model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        StringBuffer sqlStr = new StringBuffer();

        boolean flag = isSame(model, false);
        if (flag) {
            return "Same";
        }

        sqlStr.append("UPDATE pa_distrib SET ");
        if (model.getRoleId() != null && !model.getRoleId().equals("")) {
            sqlStr.append("role_id='" + model.getRoleId() + "'");
        }
        if (model.getWorksId() != null && !model.getWorksId().equals("")) {
            sqlStr.append(", works_id='" + model.getWorksId() + "'");
        }
        if (model.getDistribMemo() != null && !model.getDistribMemo().equals("")) {
            sqlStr.append(", distrib_memo='" + model.getDistribMemo() + "'");
        }
        if (model.getDistribFlag() != null && !model.getDistribFlag().equals("")) {
            sqlStr.append(", distrib_flag='" + model.getDistribFlag() + "'");
        }

        sqlStr.append(" WHERE distrib_id='" + model.getDistribId() + "'");

        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }

        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public String delete(Distrib model) {
        return null;
    }

    @Override
    public String deleteById(Long distribId) {
        Connection conn = null;
        PreparedStatement pstm = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("DELETE FROM pa_distrib WHERE distrib_id=?");
        conn = db.getConnection();
        int num = 0;

        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, distribId);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }

        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public List<Distrib> getAllData(Distrib model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Distrib> list = new ArrayList<>();

        sqlStr.append("SELECT d.*, r.role_name, w.works_name FROM pa_distrib d ");
        sqlStr.append("LEFT JOIN pa_role r ON r.role_id = d.role_id ");
        sqlStr.append("LEFT JOIN pa_works w ON w.works_id = d.works_id ");
        sqlStr.append("WHERE 1=1 ");

        if (model.getRoleId() != null && !model.getRoleId().equals("")) {
            sqlStr.append("AND d.role_id='" + model.getRoleId() + "' ");
        }
        if (model.getWorksId() != null && !model.getWorksId().equals("")) {
            sqlStr.append("AND d.works_id='" + model.getWorksId() + "' ");
        }
        if (model.getDistribFlag() != null && !model.getDistribFlag().equals("")) {
            sqlStr.append("AND d.distrib_flag='" + model.getDistribFlag() + "' ");
        }

        sqlStr.append("ORDER BY d.distrib_stmp ASC, d.distrib_id ASC");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();

            while (rs.next()) {
                Distrib md = new Distrib();
                md.setDistribId(rs.getLong("distrib_id"));
                md.setRoleId(rs.getString("role_id"));
                md.setWorksId(rs.getString("works_id"));
                md.setDistribDate(rs.getDate("distrib_date"));
                md.setDistribMemo(rs.getString("distrib_memo"));
                md.setDistribFlag(rs.getString("distrib_flag").equals("0") ? "启用" : "禁用");
                md.setDistribStmp(rs.getDate("distrib_stmp"));
                md.setRoleName(rs.getString("role_name"));
                md.setWorksName(rs.getString("works_name"));

                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public Distrib findById(Long distribId) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("SELECT d.*, r.role_name, w.works_name FROM pa_distrib d ");
        sqlStr.append("LEFT JOIN pa_role r ON r.role_id = d.role_id ");
        sqlStr.append("LEFT JOIN pa_works w ON w.works_id = d.works_id ");
        sqlStr.append("WHERE d.distrib_id=?");

        conn = db.getConnection();
        Distrib md = new Distrib();

        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, distribId);
            rs = pstm.executeQuery();

            if (rs.next()) {
                md.setDistribId(rs.getLong("distrib_id"));
                md.setRoleId(rs.getString("role_id"));
                md.setWorksId(rs.getString("works_id"));
                md.setDistribDate(rs.getDate("distrib_date"));
                md.setDistribMemo(rs.getString("distrib_memo"));
                md.setDistribFlag(rs.getString("distrib_flag").equals("0") ? "启用" : "禁用");
                md.setDistribStmp(rs.getDate("distrib_stmp"));
                md.setRoleName(rs.getString("role_name"));
                md.setWorksName(rs.getString("works_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return md;
    }

    @Override
    public Distrib find(Distrib model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("SELECT * FROM pa_distrib WHERE 1=1 ");

        if (model.getRoleId() != null && !model.getRoleId().equals("")) {
            sqlStr.append("AND role_id='" + model.getRoleId() + "' ");
        }
        if (model.getWorksId() != null && !model.getWorksId().equals("")) {
            sqlStr.append("AND works_id='" + model.getWorksId() + "' ");
        }
        if (model.getDistribFlag() != null && !model.getDistribFlag().equals("")) {
            sqlStr.append("AND distrib_flag='" + model.getDistribFlag() + "' ");
        }

        conn = db.getConnection();
        Distrib md = new Distrib();

        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();

            if (rs.next()) {
                md.setDistribId(rs.getLong("distrib_id"));
                md.setRoleId(rs.getString("role_id"));
                md.setWorksId(rs.getString("works_id"));
                md.setDistribDate(rs.getDate("distrib_date"));
                md.setDistribMemo(rs.getString("distrib_memo"));
                md.setDistribFlag(rs.getString("distrib_flag").equals("0") ? "启用" : "禁用");
                md.setDistribStmp(rs.getDate("distrib_stmp"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return md;
    }

    @Override
    public boolean isSame(Distrib model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();

        sqlStr.append("SELECT * FROM pa_distrib WHERE role_id=? AND works_id=?");

        if (!sflag) {
            sqlStr.append(" AND distrib_id!='" + model.getDistribId() + "'");
        }

        conn = db.getConnection();
        boolean flag = false;

        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getRoleId());
            pstm.setString(2, model.getWorksId());
            rs = pstm.executeQuery();

            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return flag;
    }

    @Override
    public List<Distrib> getAllDataByPage(Distrib model, Page page) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Distrib> list = new ArrayList<>();

        sqlStr.append("SELECT d.*, r.role_name, w.works_name FROM pa_distrib d ");
        sqlStr.append("LEFT JOIN pa_role r ON r.role_id = d.role_id ");
        sqlStr.append("LEFT JOIN pa_works w ON w.works_id = d.works_id ");
        sqlStr.append("WHERE 1=1 ");

        if (model.getRoleId() != null && !model.getRoleId().equals("")) {
            sqlStr.append("AND d.role_id='" + model.getRoleId() + "' ");
        }
        if (model.getWorksId() != null && !model.getWorksId().equals("")) {
            sqlStr.append("AND d.works_id='" + model.getWorksId() + "' ");
        }
        if (model.getDistribFlag() != null && !model.getDistribFlag().equals("")) {
            sqlStr.append("AND d.distrib_flag='" + model.getDistribFlag() + "' ");
        }

        sqlStr.append("ORDER BY d.distrib_stmp ASC, d.distrib_id ASC ");
        sqlStr.append("LIMIT ?, ?");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setInt(1, (page.getCurrentPage() - 1) * page.getCount());
            pstm.setInt(2, page.getCount());
            rs = pstm.executeQuery();

            while (rs.next()) {
                Distrib md = new Distrib();
                md.setDistribId(rs.getLong("distrib_id"));
                md.setRoleId(rs.getString("role_id"));
                md.setWorksId(rs.getString("works_id"));
                md.setDistribDate(rs.getDate("distrib_date"));
                md.setDistribMemo(rs.getString("distrib_memo"));
                md.setDistribFlag(rs.getString("distrib_flag").equals("0") ? "启用" : "禁用");
                md.setDistribStmp(rs.getDate("distrib_stmp"));
                md.setRoleName(rs.getString("role_name"));
                md.setWorksName(rs.getString("works_name"));

                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public int getCount(Distrib model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        int count = 0;

        sqlStr.append("SELECT COUNT(*) FROM pa_distrib WHERE 1=1 ");

        if (model.getRoleId() != null && !model.getRoleId().equals("")) {
            sqlStr.append("AND role_id='" + model.getRoleId() + "' ");
        }
        if (model.getWorksId() != null && !model.getWorksId().equals("")) {
            sqlStr.append("AND works_id='" + model.getWorksId() + "' ");
        }
        if (model.getDistribFlag() != null && !model.getDistribFlag().equals("")) {
            sqlStr.append("AND distrib_flag='" + model.getDistribFlag() + "' ");
        }

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();

            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    @Override
    public List<Distrib> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Distrib> list = new ArrayList<>();

        sqlStr.append("SELECT * FROM pa_distrib WHERE distrib_flag='0' ");
        sqlStr.append("ORDER BY distrib_date DESC");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();

            while (rs.next()) {
                Distrib md = new Distrib();
                md.setDistribId(rs.getLong("distrib_id"));
                md.setRoleId(rs.getString("role_id"));
                md.setWorksId(rs.getString("works_id"));
                md.setDistribDate(rs.getDate("distrib_date"));

                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }
}